<?php
session_start();
require_once '../config/database.php';
require_once '../includes/functions.php';

if (!isset($_SESSION['admin_id'])) {
    redirect('../index.php');
}

$success = '';
$error = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    $exam_id = $_POST['exam_id'];
    $subject_id = $_POST['subject_id'];
    
    // Validate inputs
    if (empty($exam_id) || empty($subject_id)) {
        $error = "Please select both exam and subject.";
    } else {
        // Validate file
        $allowed_types = [
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/octet-stream'
        ];
        
        $file_type = $_FILES['excel_file']['type'];
        $file_extension = strtolower(pathinfo($_FILES['excel_file']['name'], PATHINFO_EXTENSION));
        
        if (!in_array($file_type, $allowed_types) && $file_extension !== 'xlsx') {
            $error = "Please upload a valid Excel file (.xlsx only)";
        } elseif ($_FILES['excel_file']['error'] !== UPLOAD_ERR_OK) {
            $error = "File upload error: " . $_FILES['excel_file']['error'];
        } elseif ($_FILES['excel_file']['size'] > 10 * 1024 * 1024) {
            $error = "File size too large. Maximum allowed size is 10MB.";
        } else {
            try {
                $inputFileName = $_FILES['excel_file']['tmp_name'];
                $questions_data = parseXLSXFile($inputFileName);
                
                if (empty($questions_data)) {
                    $error = "No valid questions found in the file or unable to parse the file.";
                    throw new Exception($error);
                }
                
                $pdo->beginTransaction();
                $pdo->exec("SET NAMES utf8mb4");
                
                $questions_added = 0;
                $skipped_rows = 0;
                
                foreach ($questions_data as $row_index => $row) {
                    // Skip empty rows
                    if (empty(trim($row['question_text']))) {
                        $skipped_rows++;
                        continue;
                    }
                    
                    $correct_answer = strtoupper(trim($row['correct_answer']));
                    if (!in_array($correct_answer, ['A', 'B', 'C', 'D'])) {
                        $error = "Invalid correct answer '{$correct_answer}' in row " . ($row_index + 2) . ". Must be A, B, C, or D.";
                        $pdo->rollBack();
                        break;
                    }
                    
                    $marks = intval($row['marks']);
                    if ($marks < 1) {
                        $marks = 1;
                    }
                    
                    // Clean text data
                    $clean_question_text = cleanText($row['question_text']);
                    $clean_option_a = cleanText($row['option_a']);
                    $clean_option_b = cleanText($row['option_b']);
                    $clean_option_c = cleanText($row['option_c']);
                    $clean_option_d = cleanText($row['option_d']);
                    
                    // Insert question
                    $stmt = $pdo->prepare("INSERT INTO questions (exam_id, subject_id, question_text, option_a, option_b, option_c, option_d, correct_answer, marks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    $stmt->execute([
                        $exam_id, 
                        $subject_id,
                        $clean_question_text, 
                        $clean_option_a, 
                        $clean_option_b, 
                        $clean_option_c, 
                        $clean_option_d, 
                        $correct_answer, 
                        $marks
                    ]);
                    
                    $questions_added++;
                }
                
                if (empty($error)) {
                    $stmt = $pdo->prepare("UPDATE exams SET total_questions = (SELECT COUNT(*) FROM questions WHERE exam_id = ?) WHERE id = ?");
                    $stmt->execute([$exam_id, $exam_id]);
                    
                    $pdo->commit();
                    $success = "Successfully uploaded {$questions_added} questions for the selected subject!" . ($skipped_rows > 0 ? " {$skipped_rows} empty rows were skipped." : "");
                }
                
            } catch (Exception $e) {
                $pdo->rollBack();
                $error = "Error uploading file: " . $e->getMessage();
            }
        }
    }
}

/**
 * Parse XLSX file manually using ZIP and XML
 */
function parseXLSXFile($filename) {
    $questions = [];
    
    // Check if ZIP extension is available
    if (!class_exists('ZipArchive')) {
        throw new Exception("ZIP extension is required for XLSX file processing. Please enable php_zip extension in your PHP configuration.");
    }
    
    $temp_dir = sys_get_temp_dir() . '/xlsx_extract_' . uniqid();
    if (!mkdir($temp_dir, 0755, true)) {
        throw new Exception("Could not create temporary directory for XLSX extraction.");
    }
    
    $zip = new ZipArchive();
    if ($zip->open($filename) !== TRUE) {
        deleteDirectory($temp_dir);
        throw new Exception("Could not open XLSX file. File may be corrupted or not a valid XLSX file.");
    }
    
    try {
        // Extract to temporary directory
        $zip->extractTo($temp_dir);
        $zip->close();
        
        // Parse shared strings
        $sharedStrings = parseSharedStrings($temp_dir);
        
        // Parse worksheet data
        $questions = parseWorksheet($temp_dir, $sharedStrings);
        
    } catch (Exception $e) {
        deleteDirectory($temp_dir);
        throw $e;
    }
    
    deleteDirectory($temp_dir);
    return $questions;
}

/**
 * Parse shared strings from XLSX
 */
function parseSharedStrings($temp_dir) {
    $sharedStrings = [];
    $sharedStringsFile = $temp_dir . '/xl/sharedStrings.xml';
    
    if (!file_exists($sharedStringsFile)) {
        return $sharedStrings;
    }
    
    $content = file_get_contents($sharedStringsFile);
    
    // Extract all text elements from shared strings
    preg_match_all('/<t[^>]*>(.*?)<\/t>/s', $content, $matches);
    
    foreach ($matches[1] as $index => $text) {
        $sharedStrings[$index] = html_entity_decode($text, ENT_QUOTES | ENT_XML1, 'UTF-8');
    }
    
    return $sharedStrings;
}

/**
 * Parse worksheet data - UPDATED FOR YOUR FILE FORMAT
 */
function parseWorksheet($temp_dir, $sharedStrings) {
    $questions = [];
    $sheetFile = $temp_dir . '/xl/worksheets/sheet1.xml';
    
    if (!file_exists($sheetFile)) {
        throw new Exception("Could not find worksheet data in the XLSX file.");
    }
    
    $content = file_get_contents($sheetFile);
    
    // Extract all rows
    preg_match_all('/<row[^>]*>(.*?)<\/row>/s', $content, $rowMatches);
    
    foreach ($rowMatches[1] as $rowIndex => $rowContent) {
        if ($rowIndex == 0) continue; // Skip header row
        
        $rowData = parseRowData($rowContent, $sharedStrings);
        
        // UPDATED: Match your Excel file structure
        // Column A: No (skip)
        // Column B: Question
        // Column C: Option A  
        // Column D: Option B
        // Column E: Option C
        // Column F: Option D
        // Column G: Answer
        if (count($rowData) >= 7) {
            $question = [
                'question_text' => $rowData['B'] ?? '',
                'option_a' => $rowData['C'] ?? '',
                'option_b' => $rowData['D'] ?? '',
                'option_c' => $rowData['E'] ?? '',
                'option_d' => $rowData['F'] ?? '',
                'correct_answer' => $rowData['G'] ?? '',
                'marks' => 1 // Default marks since your file doesn't have marks column
            ];
            
            // Only add if we have a question
            if (!empty(trim($question['question_text']))) {
                $questions[] = $question;
            }
        }
    }
    
    return $questions;
}

/**
 * Parse individual row data
 */
function parseRowData($rowContent, $sharedStrings) {
    $rowData = [];
    
    // Extract all cells in the row with their column references
    preg_match_all('/<c[^>]*r="([A-Z])(\d+)"[^>]*>(.*?)<\/c>/s', $rowContent, $cellMatches, PREG_SET_ORDER);
    
    foreach ($cellMatches as $cellMatch) {
        $column = $cellMatch[1]; // Column letter (A, B, C, etc.)
        $cellContent = $cellMatch[3];
        
        // Extract cell value
        preg_match('/<v[^>]*>(.*?)<\/v>/s', $cellContent, $valueMatch);
        if (!isset($valueMatch[1])) {
            $rowData[$column] = '';
            continue;
        }
        
        $cellValue = trim($valueMatch[1]);
        
        // Check if cell uses shared string
        if (strpos($cellMatch[0], 't="s"') !== false) {
            // Value is an index into shared strings
            $stringIndex = intval($cellValue);
            $rowData[$column] = $sharedStrings[$stringIndex] ?? '';
        } else {
            // Direct value
            $rowData[$column] = $cellValue;
        }
    }
    
    return $rowData;
}

/**
 * Clean text data for UTF-8 encoding
 */
function cleanText($text) {
    if (empty($text)) return '';
    
    $clean = trim($text);
    $clean = preg_replace('/\xE2\x88\x92/', '-', $clean); // Replace minus sign
    $clean = html_entity_decode($clean, ENT_QUOTES | ENT_XML1, 'UTF-8');
    $clean = mb_convert_encoding($clean, 'UTF-8', 'UTF-8');
    
    return $clean;
}

/**
 * Helper function to delete directory recursively
 */
function deleteDirectory($dir) {
    if (!file_exists($dir)) return true;
    if (!is_dir($dir)) return unlink($dir);
    
    foreach (scandir($dir) as $item) {
        if ($item == '.' || $item == '..') continue;
        if (!deleteDirectory($dir . DIRECTORY_SEPARATOR . $item)) {
            return false;
        }
    }
    
    return rmdir($dir);
}

// Get exams for dropdown
$stmt = $pdo->prepare("SELECT * FROM exams ORDER BY year DESC, name");
$stmt->execute();
$exams = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get subjects for dropdown
$stmt = $pdo->prepare("SELECT * FROM subjects ORDER BY name");
$stmt->execute();
$subjects = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Upload Questions - Admin</title>
    <link rel="stylesheet" href="../assets/css/style.css">
    <style>
        .upload-container {
            background: white;
            border-radius: 15px;
            box-shadow: 0 5px 25px rgba(0,0,0,0.1);
            overflow: hidden;
            margin: 2rem 0;
        }

        .content-header {
            background: linear-gradient(135deg, #2c3e50, #34495e);
            color: white;
            padding: 2rem;
        }

        .content-header h1 {
            margin: 0;
            font-size: 2rem;
            font-weight: 700;
        }

        .content-body {
            padding: 2rem;
        }

        .alert {
            padding: 15px 20px;
            border-radius: 10px;
            margin-bottom: 2rem;
            font-weight: 500;
            border-left: 4px solid;
        }

        .alert-success {
            background: #d4edda;
            color: #155724;
            border-left-color: #28a745;
        }

        .alert-error {
            background: #f8d7da;
            color: #721c24;
            border-left-color: #dc3545;
        }

        .alert-warning {
            background: #fff3cd;
            color: #856404;
            border-left-color: #ffc107;
        }

        .upload-section {
            background: #f8f9fa;
            padding: 2rem;
            border-radius: 10px;
            margin-bottom: 2rem;
        }

        .upload-section h3 {
            color: #2c3e50;
            margin-bottom: 1.5rem;
            font-size: 1.3rem;
            display: flex;
            align-items: center;
            gap: 10px;
        }

        .upload-section h3::before {
            content: '📤';
            font-size: 1.2rem;
        }

        .form-group {
            margin-bottom: 1.5rem;
        }

        .form-group label {
            display: block;
            margin-bottom: 0.5rem;
            color: #2c3e50;
            font-weight: 600;
            font-size: 0.9rem;
        }

        .form-group input,
        .form-group select {
            width: 100%;
            padding: 12px 15px;
            border: 2px solid #e1e8ed;
            border-radius: 8px;
            font-size: 16px;
            transition: all 0.3s ease;
            background: white;
        }

        .form-group input:focus,
        .form-group select:focus {
            outline: none;
            border-color: #3498db;
            box-shadow: 0 0 0 3px rgba(52, 152, 219, 0.1);
        }

        .form-row {
            display: grid;
            grid-template-columns: 1fr 1fr;
            gap: 1rem;
        }

        @media (max-width: 768px) {
            .form-row {
                grid-template-columns: 1fr;
            }
        }

        .file-upload {
            border: 2px dashed #3498db;
            border-radius: 8px;
            padding: 2rem;
            text-align: center;
            background: #f8f9fa;
            transition: all 0.3s ease;
        }

        .file-upload:hover {
            background: #e3f2fd;
            border-color: #2980b9;
        }

        .file-upload.dragover {
            background: #d1ecf1;
            border-color: #17a2b8;
        }

        .upload-icon {
            font-size: 3rem;
            color: #3498db;
            margin-bottom: 1rem;
        }

        .btn {
            padding: 12px 25px;
            border: none;
            border-radius: 8px;
            font-size: 16px;
            font-weight: 600;
            cursor: pointer;
            transition: all 0.3s ease;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            gap: 8px;
        }

        .btn-primary {
            background: #3498db;
            color: white;
        }

        .btn-primary:hover {
            background: #2980b9;
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(52, 152, 219, 0.3);
        }

        .btn-success {
            background: #27ae60;
            color: white;
        }

        .btn-success:hover {
            background: #219a52;
            transform: translateY(-2px);
        }

        .template-section {
            background: white;
            padding: 2rem;
            border-radius: 10px;
            box-shadow: 0 3px 10px rgba(0,0,0,0.08);
        }

        .template-section h3 {
            color: #2c3e50;
            margin-bottom: 1.5rem;
            font-size: 1.3rem;
            display: flex;
            align-items: center;
            gap: 10px;
        }

        .template-section h3::before {
            content: '📋';
            font-size: 1.2rem;
        }

        .format-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 1rem;
        }

        .format-table th,
        .format-table td {
            border: 1px solid #ddd;
            padding: 12px;
            text-align: left;
        }

        .format-table th {
            background: #34495e;
            color: white;
            font-weight: 600;
        }

        .format-table tr:nth-child(even) {
            background: #f8f9fa;
        }

        .download-template {
            text-align: center;
            margin-top: 2rem;
            padding-top: 2rem;
            border-top: 1px solid #e1e8ed;
        }

        @media (max-width: 768px) {
            .content-body {
                padding: 1.5rem;
            }

            .upload-section {
                padding: 1.5rem;
            }

            .format-table {
                font-size: 0.9rem;
            }

            .format-table th,
            .format-table td {
                padding: 8px;
            }
        }
    </style>
</head>
<body>
    <?php include '../includes/header.php'; ?>
    
    <div class="container">
        <div class="upload-container">
            <div class="content-header">
                <h1>Upload Questions</h1>
                <p>Bulk upload questions from Excel files (.xlsx)</p>
            </div>

            <div class="content-body">
                <?php if (!empty($success)): ?>
                    <div class="alert alert-success"><?php echo $success; ?></div>
                <?php endif; ?>

                <?php if (!empty($error)): ?>
                    <div class="alert alert-error"><?php echo $error; ?></div>
                <?php endif; ?>

                <?php if (!class_exists('ZipArchive')): ?>
                    <div class="alert alert-warning">
                        <strong>Warning:</strong> ZIP extension is not enabled on your server. 
                        Please contact your hosting provider to enable the <code>php_zip</code> extension.
                    </div>
                <?php endif; ?>

                <div class="upload-section">
                    <h3>Upload Excel File</h3>
                    <form method="POST" enctype="multipart/form-data" id="uploadForm">
                        <div class="form-row">
                            <div class="form-group">
                                <label>Select Exam</label>
                                <select name="exam_id" required>
                                    <option value="">-- Select Exam --</option>
                                    <?php foreach ($exams as $exam): ?>
                                        <option value="<?php echo $exam['id']; ?>" <?php echo isset($_POST['exam_id']) && $_POST['exam_id'] == $exam['id'] ? 'selected' : ''; ?>>
                                            <?php echo htmlspecialchars($exam['name'] . ' (' . $exam['year'] . ')'); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>

                            <div class="form-group">
                                <label>Select Subject</label>
                                <select name="subject_id" required>
                                    <option value="">-- Select Subject --</option>
                                    <?php foreach ($subjects as $subject): ?>
                                        <option value="<?php echo $subject['id']; ?>" <?php echo isset($_POST['subject_id']) && $_POST['subject_id'] == $subject['id'] ? 'selected' : ''; ?>>
                                            <?php echo htmlspecialchars($subject['name']); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                        
                        <div class="form-group">
                            <label>Excel File (.xlsx only)</label>
                            <div class="file-upload" id="fileUploadArea">
                                <div class="upload-icon">📤</div>
                                <h4>Drag & Drop your Excel file here</h4>
                                <p>or</p>
                                <input type="file" name="excel_file" accept=".xlsx" required 
                                       style="display: none;" id="fileInput">
                                <button type="button" class="btn btn-primary" onclick="document.getElementById('fileInput').click()">
                                    📁 Choose File
                                </button>
                                <div id="fileName" style="margin-top: 1rem; color: #7f8c8d;"></div>
                            </div>
                            <small style="color: #7f8c8d; display: block; margin-top: 0.5rem;">
                                Supported format: .xlsx only (Max: 10MB)
                            </small>
                        </div>
                        
                        <button type="submit" class="btn btn-success" id="uploadBtn" <?php echo !class_exists('ZipArchive') ? 'disabled' : ''; ?>>
                            ⬆️ Upload Questions
                        </button>
                        
                        <?php if (!class_exists('ZipArchive')): ?>
                            <div style="color: #dc3545; margin-top: 10px; font-weight: 500;">
                                ❌ Upload disabled: ZIP extension required
                            </div>
                        <?php endif; ?>
                    </form>
                </div>

                <div class="template-section">
                    <h3>Excel File Format (Updated)</h3>
                    <p>Your Excel file should have the following columns in order (starting from row 2):</p>
                    
                    <table class="format-table">
                        <thead>
                            <tr>
                                <th width="80">Column</th>
                                <th>Content</th>
                                <th>Required</th>
                                <th>Example</th>
                                <th>Notes</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>A</td>
                                <td>Question Number</td>
                                <td>Optional</td>
                                <td>1</td>
                                <td>Auto-numbering, can be omitted</td>
                            </tr>
                            <tr>
                                <td>B</td>
                                <td>Question Text</td>
                                <td><strong>Required</strong></td>
                                <td>What is the capital of Nigeria?</td>
                                <td>Main question content</td>
                            </tr>
                            <tr>
                                <td>C</td>
                                <td>Option A</td>
                                <td><strong>Required</strong></td>
                                <td>Lagos</td>
                                <td>First multiple choice option</td>
                            </tr>
                            <tr>
                                <td>D</td>
                                <td>Option B</td>
                                <td><strong>Required</strong></td>
                                <td>Abuja</td>
                                <td>Second multiple choice option</td>
                            </tr>
                            <tr>
                                <td>E</td>
                                <td>Option C</td>
                                <td><strong>Required</strong></td>
                                <td>Kano</td>
                                <td>Third multiple choice option</td>
                            </tr>
                            <tr>
                                <td>F</td>
                                <td>Option D</td>
                                <td><strong>Required</strong></td>
                                <td>Port Harcourt</td>
                                <td>Fourth multiple choice option</td>
                            </tr>
                            <tr>
                                <td>G</td>
                                <td>Correct Answer</td>
                                <td><strong>Required</strong></td>
                                <td>B</td>
                                <td>Must be A, B, C, or D (case insensitive)</td>
                            </tr>
                        </tbody>
                    </table>

                    <div class="download-template">
                        <p><strong>Note:</strong> All questions will be assigned 1 mark by default</p>
                        <a href="download_template.php" class="btn btn-primary">
                            📥 Download Template
                        </a>
                    </div>
                </div>
            </div>
        </div>
    </div>
<br/>
<?php include '../includes/footer.php'; ?> 
    <script>
        // File upload drag and drop functionality
        const fileUploadArea = document.getElementById('fileUploadArea');
        const fileInput = document.getElementById('fileInput');
        const fileName = document.getElementById('fileName');
        const uploadForm = document.getElementById('uploadForm');
        const uploadBtn = document.getElementById('uploadBtn');

        // Drag and drop events
        fileUploadArea.addEventListener('dragover', function(e) {
            e.preventDefault();
            this.classList.add('dragover');
        });

        fileUploadArea.addEventListener('dragleave', function(e) {
            e.preventDefault();
            this.classList.remove('dragover');
        });

        fileUploadArea.addEventListener('drop', function(e) {
            e.preventDefault();
            this.classList.remove('dragover');
            
            const files = e.dataTransfer.files;
            if (files.length > 0) {
                const file = files[0];
                if (file.name.toLowerCase().endsWith('.xlsx')) {
                    fileInput.files = files;
                    updateFileName();
                } else {
                    alert('Please select only .xlsx files');
                }
            }
        });

        fileInput.addEventListener('change', function() {
            const file = this.files[0];
            if (file && !file.name.toLowerCase().endsWith('.xlsx')) {
                alert('Please select only .xlsx files');
                this.value = '';
                fileName.innerHTML = '';
            } else {
                updateFileName();
            }
        });

        function updateFileName() {
            if (fileInput.files.length > 0) {
                const file = fileInput.files[0];
                fileName.innerHTML = `
                    <strong>Selected file:</strong> ${file.name}<br>
                    <small>Size: ${(file.size / 1024 / 1024).toFixed(2)} MB</small>
                `;
            } else {
                fileName.innerHTML = '';
            }
        }

        // Form submission loading state
        uploadForm.addEventListener('submit', function() {
            uploadBtn.innerHTML = '⏳ Uploading...';
            uploadBtn.disabled = true;
        });

        // Auto-hide alerts after 5 seconds
        setTimeout(function() {
            const alerts = document.querySelectorAll('.alert');
            alerts.forEach(alert => {
                alert.style.opacity = '0';
                alert.style.transition = 'opacity 0.5s ease';
                setTimeout(() => alert.remove(), 500);
            });
        }, 5000);
    </script>
</body>
</html>